package com.ndp.fb.rdb.db;

import com.alibaba.fastjson.JSON;
import com.mysql.jdbc.Statement;
import com.ndp.fb.rdb.annotation.InsertValuePair;
import com.ndp.fb.rdb.db.impl.mapper.AutoRowMapper;
import com.ndp.fb.rdb.model.Pager;
import com.ndp.fb.util.ReflectUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by yeahmobi on 2014/10/22.
 */
public class DaoTemplate {
    @Autowired
    private JdbcTemplateMixed jdbcTemplateMixed;
    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    public JdbcTemplateMixed getJdbcTemplate() {
        return jdbcTemplateMixed;
    }

    /**
     * 查询一个字段
     *
     * @param columnName
     * @param columnValue
     * @param rowMapper
     * @param <T>
     * @return
     */
    public <T> List<T> findByProperty(String columnName, Object columnValue, AutoRowMapper<T> rowMapper) {
        Class<T> clazz = rowMapper.getClazz();
        String tableName = ReflectUtil.getTableName(clazz);
        if (StringUtils.isEmpty(tableName)) {
            throw new RuntimeException(clazz + " not found tableName");
        }
        if (columnValue == null) {
            throw new RuntimeException(" findByProperty columnValue is null");
        }
        String sql = "select * from " + tableName + " where " + columnName + " = ?";
        logger.debug(sql);
        logger.debug(columnValue.toString());
        return getJdbcTemplate().query(sql, new Object[]{columnValue}, rowMapper);
    }

    /**
     * 查询一个字段
     *
     * @param columnName
     * @param columnValue
     * @param rowMapper
     * @param <T>
     * @return
     */
    public <T> T findOneByProperty(String columnName, Object columnValue, AutoRowMapper<T> rowMapper) {
        List<T> list = findByProperty(columnName, columnValue, rowMapper);
        if (list.size() > 0) {
            return list.get(0);
        } else {
            return null;
        }
    }

    /**
     * 获取所有的数据
     *
     * @param rowMapper
     * @param <T>
     * @return
     */
    public <T> List<T> findAll(AutoRowMapper<T> rowMapper) {
        Class<T> clazz = rowMapper.getClazz();
        String tableName = ReflectUtil.getTableName(clazz);
        if (StringUtils.isEmpty(tableName)) {
            throw new RuntimeException(clazz + " not found tableName");
        }
        String sql = "select * from " + tableName;
        return getJdbcTemplate().query(sql, rowMapper);
    }


    /**
     * 执行插入sql，返回指定的ID
     *
     * @param sql
     * @param pstAssign
     * @return
     */
    public Long saveResultId(final String sql, final PstAssign pstAssign) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(con -> {
            PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (pstAssign != null) {
                pstAssign.setParam(ps);
            }
            return ps;
        }, keyHolder);
        return keyHolder.getKey().longValue();
    }

    /**
     * @return
     */
    public <T> Long saveResultId(T obj) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(con -> {
            PreparedStatement ps = null;
            Class<?> clazz = obj.getClass();
            String tableName = ReflectUtil.getTableName(clazz);
            if (StringUtils.isNoneEmpty(tableName)) {
                ps = getPreparedStatemet(tableName, obj, con);
            }
            if (ps == null)
                throw new RuntimeException("check" + obj.getClass() + " Table annotation， save failed !");
            return ps;
        }, keyHolder);
        return keyHolder.getKey().longValue();
    }


    /**
     * 执行插入sql，返回指定的ID
     *
     * @param sql
     * @param pstAssign
     * @return
     */
    public void save(final String sql, final PstAssign pstAssign) {
        getJdbcTemplate().update(con -> {
            PreparedStatement ps = con.prepareStatement(sql);
            if (pstAssign != null) {
                pstAssign.setParam(ps);
            }
            return ps;
        });
    }

    /**
     * 批量保存，返回批量的值
     *
     * @param sql
     * @param batchPstAssign
     * @param list
     * @return
     */
    public <T> List<Long> batchSaveResultId(final String sql, final BatchPstAssign<T> batchPstAssign, final List<T> list) {
        return getJdbcTemplate().execute((ConnectionCallback<List<Long>>) conn -> {
                    final int count = list.size();
                    List<Long> ids = new ArrayList<>(count);
                    conn.setAutoCommit(false);
                    PreparedStatement ps = null;
                    ResultSet resultSet = null;
                    try {
                        ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
                        for (T t : list) {
                            batchPstAssign.setParam(ps, t);
                            ps.addBatch();
                        }
                        ps.executeBatch();
                        resultSet = ps.getGeneratedKeys();
                        while (resultSet.next()) {
                            ids.add(resultSet.getLong(1));
                        }
                        conn.commit();
                        resultSet.close();
                        return ids;
                    } catch (Exception e) {
                        conn.rollback();
                        throw e;
                    } finally {
                        JdbcUtils.closeResultSet(resultSet);
                        JdbcUtils.closeStatement(ps);
                    }
                }
        );
    }


    /**
     * 批量保存，返回批量的值
     *
     * @param list
     * @return
     */
    public <T> List<Long> batchSaveResultId(final List<T> list) {
        List<Long> result = new ArrayList<>();
        if (!list.isEmpty()) {
            for (T t : list) {
                result.add(saveResultId(t));
            }
        }
        return result;
    }

    public PreparedStatement getPreparedStatemet(String tableName, Object obj, Connection con) throws SQLException {
        //all fields
        List<InsertValuePair> insertValuePairList = ReflectUtil.getInsertValuePairList(obj.getClass());
        // not null values
        List<Object> values = new ArrayList<>();
        StringBuilder insertSql = new StringBuilder();
        if (insertValuePairList.size() > 0) {
            StringBuilder valueSql = new StringBuilder();
            insertSql.append(" insert into ").append(tableName).append(" ( ");
            valueSql.append(" values( ");
            for (int i = 0, len = insertValuePairList.size(); i < len; i++) {
                InsertValuePair insertValuePair = insertValuePairList.get(i);
                Object fieldValue = ReflectUtil.getFieldValue(obj, insertValuePair.getMethod(), insertValuePair.getField());
                if (fieldValue == null) {
                    logger.info("Class:{},属性：{}没有值。", obj.getClass().getSimpleName(), insertValuePair.getField().getName());
                } else {
                    values.add(fieldValue);
                    String dbColumnName = insertValuePair.getColumnName();
                    insertSql.append(dbColumnName).append(",");
                    valueSql.append("?,");
                }
            }
            insertSql.deleteCharAt(insertSql.length() - 1);
            valueSql.deleteCharAt(valueSql.length() - 1);
            insertSql.append(")").append(valueSql).append(")");
            logger.debug("insertSql >>>> " + insertSql.toString());
        }
        PreparedStatement ps = con.prepareStatement(insertSql.toString(), Statement.RETURN_GENERATED_KEYS);
        setValues(ps, values);
        return ps;
    }

    private void setValues(PreparedStatement ps, List<Object> values) throws SQLException {
        for (int i = 0; i < values.size(); i++) {
            ps.setObject(i + 1, values.get(i));
        }
    }

    public Pager findPager(String sql, Object[] params, Integer firstResult, Integer sizeNo, RowMapper rowMapper) {
        logger.debug("sql >>>> " + sql);
        logger.debug("params >>>>> " + JSON.toJSONString(params));
        logger.debug(" firstResult >>>> " + firstResult);
        logger.debug(" sizeNo >>>> " + sizeNo);
        Pager pager = new Pager();
        StringBuilder countSql = new StringBuilder("select count(1) cnt from (");
        countSql.append(sql);
        countSql.append(") as pagertable");
        if (params == null || params.length == 0) {
            Object count = getJdbcTemplate().queryForMap(countSql.toString()).get("cnt");
            logger.debug("总数 >>>> " + count.toString());
            pager.setTotalSize(Long.parseLong(count.toString()));

            sql = sql + " LIMIT ?,?";
            Object[] selectParams = new Object[]{firstResult, sizeNo};
            logger.debug("sql >>> " + sql);
            logger.debug("param >>> " + JSON.toJSONString(selectParams));
            pager.setResultList(getJdbcTemplate().query(sql, selectParams, rowMapper));
        } else {
            Object count = getJdbcTemplate().queryForMap(countSql.toString(), params).get("cnt");
            logger.debug("总数 >>>> " + count.toString());
            pager.setTotalSize(Long.parseLong(count.toString()));

            sql = sql + " LIMIT ?,?";
            Object[] selectParams = new Object[params.length + 2];
            for (int i = 0; i < params.length; i++) {
                selectParams[i] = params[i];
            }
            selectParams[params.length] = firstResult;
            selectParams[params.length + 1] = sizeNo;
            logger.debug("sql >>> " + sql);
            logger.debug("param >>> " + JSON.toJSONString(selectParams));
            pager.setResultList(getJdbcTemplate().query(sql, selectParams, rowMapper));
        }
        return pager;
    }

    public void executeSql(String sql) {
        getJdbcTemplate().execute(sql);
    }

    public <T> T findOne(String sql, Object[] params, AutoRowMapper<T> rowMapper) {
        List<T> list;
        if (params != null) {
            list = getJdbcTemplate().query(sql, params, rowMapper);
        } else {
            list = getJdbcTemplate().query(sql, rowMapper);
        }

        if (list.size() > 0) {
            return list.get(0);
        } else {
            return null;
        }
    }

    public <T> T findOneDefault(String sql, Object[] params, RowMapper<T> rowMapper) {
        List<T> list;
        if (params != null) {
            list = getJdbcTemplate().query(sql, params, rowMapper);
        } else {
            list = getJdbcTemplate().query(sql, rowMapper);
        }

        if (list.size() > 0) {
            return list.get(0);
        } else {
            return null;
        }
    }
}
